package com.jielin.handling.util;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

    private ExcelUtil() {
    }

    private final String excel2003L = ".xls";//2003- 版本的excel
    private final String excel2007U = ".xlsx";//2007+ 版本的excel

    /**
     * 根据文件后缀，自适应上传文件的版本
     */
    private Workbook getWorkbook(InputStream inStr, String filename) throws Exception {
        Workbook workbook;
        String fileType = filename.substring(filename.lastIndexOf("."));
        if (excel2003L.equals(fileType)) {
            workbook = new HSSFWorkbook(inStr);//2003-
        } else if (excel2007U.equals(fileType)) {
            workbook = new XSSFWorkbook(inStr);//2007+
        } else {
            throw new Exception("解析的文件格式有误");
        }
        return workbook;
    }

    /**
     * 获取excel中的数据
     */
    public List<List<Object>> readExcelData(InputStream in, String filename) throws Exception {
        List<List<Object>> list;

        //创建Excel工作薄
        Workbook work = getWorkbook(in, filename);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<List<Object>>();
        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }

            //遍历当前sheet中的所有行
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null || row.getFirstCellNum() == j) {
                    continue;
                }

                //遍历所有的列
                List<Object> li = new ArrayList<Object>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    //通过getCellValue方法获取当前行每一列中的数据
                    li.add(getCellValue(cell));
                }
                //将每一行的数据添加到list
                list.add(li);
            }
        }
        in.close();
        return list;
    }

    /**
     * 获取每个单元格的内容
     */
    private Object getCellValue(Cell cell) {
        Object value = null;

        DecimalFormat df = new DecimalFormat("0");//格式化number String字符串
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//日期格式化

        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    value = df.format(cell.getNumericCellValue());
                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                    value = sdf.format(cell.getDateCellValue());
                } else {
                    value = cell.getNumericCellValue();
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            default:
                break;
        }
        return value;
    }

    /**
     * 数据转成excel
     *
     * @param dataList   需要转换的数据
     * @param sheetName  生成的excel表名
     * @param columnName 每一列的名字(key)
     * @return
     */
    public Workbook dataToExcel(List<Map<String, String>> dataList,String sheetName, String[] columnName,String parentUnitName,String getMon) {

        int columnNum = columnName.length;
        // 创建HSSFWorkbook对象(excel的文档对象)
        Workbook workbook = new HSSFWorkbook();
        //创建第一页，并命名// 建立新的sheet对象（excel的表单） 并设置sheet名字
        Sheet sheet = workbook.createSheet(sheetName);

        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for (int i = 0; i < columnName.length; i++) {
            sheet.setColumnWidth(i, (int) (35.7 * 150));//此方法为设置列的宽度大小，且从0开始

        }


        //----------------标题样式-----------------------------------------------------------------
        // 建立新的sheet对象（excel的表单） 并设置sheet名字
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        Font ztFont = workbook.createFont();
        ztFont.setItalic(false);// 设置字体为斜体字0
        ztFont.setFontHeightInPoints((short)18);// 将字体大小设置为16px
        ztFont.setFontName("微软雅黑");// 将“宋体”字体应用到当前单元格上
        ztFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗
        titleStyle.setFont(ztFont);

        // 创建第二种字体
        CellStyle titleStyle2 = workbook.createCellStyle();
        titleStyle2.setBorderLeft(CellStyle.BORDER_THIN);
        titleStyle2.setBorderRight(CellStyle.BORDER_THIN);
        titleStyle2.setBorderTop(CellStyle.BORDER_THIN);
        titleStyle2.setBorderBottom(CellStyle.BORDER_THIN);
        titleStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        titleStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        Font f = workbook.createFont();
        f.setItalic(false);// 设置字体为斜体字0
        f.setFontHeightInPoints((short)8);// 将字体大小设置为8px
        f.setFontName("微软雅黑");// 将“宋体”字体应用到当前单元格上
        f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗
        titleStyle2.setFont(f);



        // 创建第三种字体
        CellStyle cs = workbook.createCellStyle();
        cs.setFont(f);
        cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setAlignment(CellStyle.ALIGN_CENTER);



        // 设置第二种单元格的样式（用于值）
        CellStyle cs2 = workbook.createCellStyle();
        cs2.setAlignment(HSSFCellStyle.ALIGN_CENTER);//平行居中
        cs2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        Font f2 = workbook.createFont();
        cs2.setFont(f2);
        cs2.setBorderLeft(CellStyle.BORDER_THIN);
        cs2.setBorderRight(CellStyle.BORDER_THIN);
        cs2.setBorderTop(CellStyle.BORDER_THIN);
        cs2.setBorderBottom(CellStyle.BORDER_THIN);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);






        //创建第一行
        Row row = sheet.createRow(0);
        row.setHeight((short)1100);

        // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        Cell cell2 = row.createCell(0);
        // 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        CellRangeAddress cra0=new CellRangeAddress(0, 0, 0, 6);
        sheet.addMergedRegion(cra0);
        RegionUtil.setBorderBottom(1, cra0, sheet,workbook); // 下边框
        RegionUtil.setBorderLeft(1, cra0, sheet,workbook); // 左边框
        RegionUtil.setBorderRight(1, cra0, sheet,workbook); // 有边框
        RegionUtil.setBorderTop(1, cra0, sheet,workbook); // 上边框
        cell2.setCellValue(parentUnitName+"月度考核详情信息");
        cell2.setCellStyle(titleStyle);





        //
        CellStyle cs5 = workbook.createCellStyle();
        cs5.setAlignment(HSSFCellStyle.ALIGN_CENTER);//平行居中
        cs5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        cs5.setBorderLeft(CellStyle.BORDER_THIN);
        cs5.setBorderRight(CellStyle.BORDER_THIN);
        cs5.setBorderTop(CellStyle.BORDER_THIN);
        cs5.setBorderBottom(CellStyle.BORDER_THIN);



        CellStyle cs4 = workbook.createCellStyle();
        cs4.setBorderLeft(CellStyle.BORDER_THIN);
        cs4.setBorderRight(CellStyle.BORDER_THIN);
        cs4.setBorderTop(CellStyle.BORDER_THIN);
        cs4.setBorderBottom(CellStyle.BORDER_THIN);
        cs4.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cs4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        Font f1 = workbook.createFont();
        f1.setItalic(false);// 设置字体为斜体字0
        f1.setFontHeightInPoints((short)8);// 将字体大小设置为8px
        f1.setFontName("微软雅黑");// 将“宋体”字体应用到当前单元格上
        f1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗
        titleStyle2.setFont(f1);


        //创建第二行
        Row row1 = sheet.createRow(1);
        row1.setHeight((short)700);
        Cell cell1 = row1.createCell(0);
        CellRangeAddress cra=new CellRangeAddress(1, 1, 0, 1);
        sheet.addMergedRegion(cra);
        RegionUtil.setBorderBottom(1, cra, sheet,workbook); // 下边框
        RegionUtil.setBorderLeft(1, cra, sheet,workbook); // 左边框
        RegionUtil.setBorderRight(1, cra, sheet,workbook); // 有边框
        RegionUtil.setBorderTop(1, cra, sheet,workbook); // 上边框
        cell1.setCellValue(parentUnitName);
        cell1.setCellStyle(titleStyle2);


        Cell cell4 = row1.createCell(5); // 填表时间
        CellRangeAddress cra2=new CellRangeAddress(1, 1, 5, 6);
        sheet.addMergedRegion(cra2);
        RegionUtil.setBorderBottom(1, cra2, sheet,workbook); // 下边框
        RegionUtil.setBorderLeft(1, cra2, sheet,workbook); // 左边框
        RegionUtil.setBorderRight(1, cra2, sheet,workbook); // 有边框
        RegionUtil.setBorderTop(1, cra2, sheet,workbook); // 上边框
        cell4.setCellValue(DateUtil.getMinusFormatDate(new Date()));
        cell4.setCellStyle(titleStyle2);












        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        //f.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

//
//        Row row2=sheet.createRow(1);
//        //设置列名
//        for (int i = 0; i < columnNum; i++) {
//            Cell cell = row2.createCell(i);
//            cell.setCellValue(columnName[i]);
//            cell.setCellStyle(cs);
//        }
//        //设置每行每列的值
//        int rowNum = dataList.size();
//        for (int j = 2; j <= rowNum; j++){
//            //创建一行0
//
//            Row r = sheet.createRow(j);
//            for (int k = 0; k < columnNum; k++) {
//                Cell cell = r.createCell(k);
//                cell.setCellValue(dataList.get(j - 2).get(columnName[k]));
//                cell.setCellStyle(cs2);
//            }
//        }
//        return workbook;

        Row row2 = sheet.createRow(2);
        row2.setHeight((short)700);
        //设置列名
        for (int i = 0; i < columnNum; i++) {
            Cell cell = row2.createCell(i);
            cell.setCellValue(columnName[i]);
            cell.setCellStyle(titleStyle2);
        }
        //设置每行每列的值
        int rowNum = 3;
        for (int j = 0; j < dataList.size(); j++){
            //创建一行0

            Row r = sheet.createRow( rowNum++ );
            r.setHeight((short)500);
            for (int k = 0; k < columnNum; k++) {
                Cell cell = r.createCell(k);
                cell.setCellValue(dataList.get(j).get(columnName[k]));
                cell.setCellStyle(cs2);
            }
        }


        // 创建底部字体
        CellStyle cs3 = workbook.createCellStyle();
        cs3.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//平行居右
        cs3.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);//垂直居下
        cs3.setBorderLeft(CellStyle.BORDER_THIN);
        cs3.setBorderRight(CellStyle.BORDER_THIN);
        cs3.setBorderTop(CellStyle.BORDER_THIN);
        cs3.setBorderBottom(CellStyle.BORDER_THIN);





        Row row3 = sheet.createRow(dataList.size()+3);
        row3.setHeight((short)400);

        // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        Cell cell3 = row3.createCell(0);

        // 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        CellRangeAddress cra3=new CellRangeAddress(dataList.size()+3, dataList.size()+3, 0, 6);
        sheet.addMergedRegion(cra3);
        RegionUtil.setBorderBottom(1, cra3, sheet,workbook); // 下边框
        RegionUtil.setBorderLeft(1, cra3, sheet,workbook); // 左边框
        RegionUtil.setBorderRight(1, cra3, sheet,workbook); // 有边框
        RegionUtil.setBorderTop(1, cra3, sheet,workbook); // 上边框
        cell3.setCellValue(parentUnitName+"在"+getMon+"考核详情信息");
        cell3.setCellStyle(cs3);
        System.out.println(getMon);

        return workbook;
    }

    public void setBorder(CellRangeAddress cellRangeAddress, Sheet sheet,
                          Workbook wb) throws Exception {
        RegionUtil.setBorderLeft(1, cellRangeAddress, sheet, wb);
        RegionUtil.setBorderBottom(1, cellRangeAddress, sheet, wb);
        RegionUtil.setBorderRight(1, cellRangeAddress, sheet, wb);
        RegionUtil.setBorderTop(1, cellRangeAddress, sheet, wb);

    }

    public static final ExcelUtil getInstance() {
        return ExcelUtilHolder.instance;
    }

    private static final class ExcelUtilHolder {
        private static final ExcelUtil instance = new ExcelUtil();
    }
}